import pymysql
from typing import Optional, List
import os
from pathlib import Path
from dotenv import load_dotenv

load_dotenv()

class MySQLConnection:
    """MySQL connection utility class with SQL file management"""
    
    def __init__(self, host: str = None, port: int = None, user: str = None, 
                 password: str = None, database: str = None, sql_dir: str = None):
        self.host = host or os.getenv('MYSQL_HOST', 'localhost')
        self.port = port or int(os.getenv('MYSQL_PORT', 10000))
        self.user = user or os.getenv('MYSQL_USER', 'root')
        self.password = password or os.getenv('MYSQL_PASSWORD', 'pass4Zentao')
        self.database = database or os.getenv('MYSQL_DATABASE', 'emailqa')
        
        # SQL directory for table definitions
        self.sql_dir = Path(sql_dir) if sql_dir else Path(__file__).parent.parent.parent / 'sql' / 'mysql'
    
    def get_connection(self):
        """Get a MySQL database connection"""
        try:
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            return connection
        except pymysql.Error as e:
            raise Exception(f"Failed to connect to MySQL: {e}")
    
    def get_connection_raw(self):
        """Get a MySQL connection without specifying database"""
        try:
            connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            return connection
        except pymysql.Error as e:
            raise Exception(f"Failed to connect to MySQL: {e}")
    
    def test_connection(self) -> bool:
        """Test database connection"""
        try:
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute("SELECT 1")
                    return True
        except Exception as e:
            print(f"[DEBUG] Connection test failed: {e}")
            return False
    
    def get_sql_files(self) -> List[Path]:
        """Get sorted list of SQL files"""
        if not self.sql_dir.exists():
            print(f"SQL directory not found: {self.sql_dir}")
            return []
        
        # Get all .sql files and sort by filename
        sql_files = [f for f in self.sql_dir.glob('*.sql')]
        sql_files.sort(key=lambda x: x.name)
        
        return sql_files
    
    def execute_sql_file(self, file_path: Path) -> bool:
        """Execute a single SQL file"""
        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                sql_content = f.read()
            
            # Remove comments and empty lines
            lines = [line.strip() for line in sql_content.split('\n') 
                     if line.strip() and not line.strip().startswith('--')]
            sql_content = '\n'.join(lines)
            
            # Split into individual statements
            statements = [stmt.strip() for stmt in sql_content.split(';') if stmt.strip()]
            
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    for statement in statements:
                        if statement:
                            cursor.execute(statement)
                    conn.commit()
                    
            print(f"[OK] Executed: {file_path.name}")
            return True
            
        except Exception as e:
            print(f"[FAIL] Error executing {file_path.name}: {e}")
            return False
    
    def table_exists(self, table_name: str) -> bool:
        """Check if a table exists in the database"""
        try:
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute("""
                        SELECT COUNT(*) as count 
                        FROM information_schema.tables 
                        WHERE table_schema = %s AND table_name = %s
                    """, (self.database, table_name))
                    result = cursor.fetchone()
                    return result['count'] > 0
        except Exception:
            return False
    
    def initialize_database(self) -> bool:
        """Initialize database with SQL files"""
        try:
            # Test connection first
            if not self.test_connection():
                print("Failed to connect to database")
                return False
            
            # Get SQL files
            sql_files = self.get_sql_files()
            if not sql_files:
                print("No SQL files found")
                return False
            
            print(f"Found {len(sql_files)} SQL files")
            
            # Execute each SQL file
            success_count = 0
            for sql_file in sql_files:
                if self.execute_sql_file(sql_file):
                    success_count += 1
            
            if success_count == len(sql_files):
                print("Database initialized successfully")
                return True
            else:
                print(f"Database partially initialized ({success_count}/{len(sql_files)} files)")
                return False
                
        except Exception as e:
            print(f"Failed to initialize database: {e}")
            return False
    
    def get_database_info(self) -> dict:
        """Get database information"""
        try:
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    # Get table list
                    cursor.execute("""
                        SELECT table_name, table_comment 
                        FROM information_schema.tables 
                        WHERE table_schema = %s
                        ORDER BY table_name
                    """, (self.database,))
                    tables = cursor.fetchall()
                    
                    return {
                        'database': self.database,
                        'host': self.host,
                        'port': self.port,
                        'connected': True,
                        'tables': tables
                    }
                    
        except Exception as e:
            return {
                'database': self.database,
                'host': self.host,
                'port': self.port,
                'connected': False,
                'error': str(e)
            }